package sqlite;


import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import sqlite.domain.OrderInfo;
import sqlite.domain.OrderItems;
import sqlite.domain.OrderShippingInfo;
import sqlite.domain.StoreStorage;
import sqlite.handler.BeanListHandler;
import sqlite.utils.DBPool;
import sqlite.utils.DBUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: 甘竞存
 * Date: 14-11-8
 * Time: 下午3:00
 * 欧伦堡erp
 */
public class OlbExternalErpServiceImpl {

    private static Logger logger = LoggerFactory.getLogger(OlbExternalErpServiceImpl.class);

    private static String DBNAME;
    private static String USERNAME;
    private static String PASSWORD;
    private static String URL;


    private Connection getConnection(){
        Connection connection = DBPool.getInstance().getConnection(URL, USERNAME, PASSWORD);
        return connection;
    }


    private void saveOrderItem(OrderItems orderItem) throws SQLException {
        String sql = "insert into Order_goods(order_no,num,price,je,outer_sku_id,sku_properties_name) values(?,?,?,?,?,?)";
        Object[] params = { orderItem.getOrderNo(), orderItem.getQuantity(),orderItem.getPrice(),orderItem.getTotalPrice(), orderItem.getSkuOuterId(),orderItem.getSkuName()};
        DBUtils.execute(getConnection(), sql, params);
    }

    private void saveOrderInfo(OrderInfo o) throws SQLException {
        String sql = "insert into Order_info(order_no,Warehouse_code,buyer_nick,Shipping_type,Shipping_code,type,created,payment,post_fee,total_fee,pay_time,receiver_name,receiver_state,receiver_city,receiver_district,receiver_address,receiver_mobile,E_CREATE_TIME,BS_FLAG) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        Object[] params = {o.getOrderNo(),o.getStoreId(),o.getCustomerName(),o.getShippingType(),o.getShippingCode(),o.getOrderType(),
                parseDate(o.getSubmitDate()),o.getPayMent(),o.getTotalShipping(),o.getSubTotal(),
                parseDate(o.getPayDate()),o.getName(),o.getProvince(),o.getCity(),o.getDistrict(),o.getStreetAddress(),o.getMobile(),
                parseDate(o.getCreateTime()),o.getFlag()};
        DBUtils.execute(getConnection(), sql, params);
    }
    private void updateOrderInfo(OrderInfo o) {
        String sql = "update Order_info set Warehouse_code=?,receiver_name=?,receiver_address=?,receiver_mobile=?,BS_FLAG=?,F_UPDATE_TIME=? where order_no=?";
        Object[] params = {o.getStoreId(),o.getName(),o.getStreetAddress(),o.getMobile(),"X", parseDate(new Date()), o.getOrderNo() };
        try {
            DBUtils.execute(getConnection(), sql, params);
        } catch (SQLException e) {
            logger.error("数据库读取异常,异常信息:"+e.getMessage());
        }
    }


    private String parseDate(Date date){
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return sdf.format(date);
    }

    public void syncOrdersSendStatusFromErp() {
        logger.info("定时获取订单发货状态,10分钟一次");
        //1 从数据库获取发货订单的数据
        List<OrderShippingInfo> all = findAllOrderShippingInfo();
        if (all==null||all.size()==0){
            logger.info("没有可以同步发货状态的订单!");
            return;
        }
        for (OrderShippingInfo o : all) {

            updateOrderShppingInfo(o);
        }
    }

    private void updateOrderShppingInfo(OrderShippingInfo orderShippingInfo) {
        String sql = "update Order_rt_info set BS_FLAG=?,F_UPDATE_TIME=? where order_no=?";
        Object[] params = { "Y", parseDate(new Date()), orderShippingInfo.getOrderNo() };
        try {
            DBUtils.execute(getConnection(), sql, params);
        } catch (SQLException e) {
            logger.error("数据库读取异常,异常信息:"+e.getMessage());
        }
    }

    private List<OrderShippingInfo> findAllOrderShippingInfo() {
        List list = null;
        String sql = "select * from Order_rt_info where BS_FLAG=?";
        Object[] params = {"X"};
        BeanListHandler rsh = new BeanListHandler(OrderShippingInfo.class);
        try {
            list = (List) DBUtils.find(getConnection(),sql, params, rsh);
        } catch (Exception e) {
            logger.error("数据库读取异常,异常信息:"+e.getMessage());
        }
        return list;
    }



    /**  ---------- 同步库存 ----------------*/
    public void syncProductStock() {
        logger.info("每5分钟从中间库抓取数据,同步库存,开始！");
        //在这里写实现：
        //1 从中间库取得所有flag为X 的数据
        List<StoreStorage> all = findAllStoreStorage();
        if (all==null||all.size()==0){
            logger.info("没有可以同步的库存变动记录!");
            return;
        }
        for (StoreStorage storeStorage : all) {

            updateStoreStorage(storeStorage);

        }

    }


    private void updateStoreStorage(StoreStorage storeStorage) {
        String sql = "update SPKCB_HZ set BS_FLAG=?,F_UPDATE_TIME=? where CKDM=? and SKU=? and BS_FLAG=?";
        Object[] params = { "Y", parseDate(new Date()), storeStorage.getStoreId(),storeStorage.getSkuOuterId(),"X" };
        try {
            DBUtils.execute(getConnection(), sql, params);
        } catch (SQLException e) {
            logger.error("数据库读取异常,异常信息:"+e.getMessage());
        }
    }

    private List<StoreStorage> findAllStoreStorage() {
        List list = null;
        String sql = "select * from SPKCB_HZ where BS_FLAG=?";
        Object[] params = {"X"};
        BeanListHandler rsh = new BeanListHandler(StoreStorage.class);
        try {
            list = (List) DBUtils.find(getConnection(),sql, params, rsh);
        } catch (Exception e) {
            logger.error("读取数据库异常,异常信息:"+e.getMessage());
        }
        return list;
    }












}
